﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using System.Xml.Xsl;
using System.Xml;
using System.Xml.XPath;
using System.Xml.Serialization;
using Microsoft.SharePoint;
using Microsoft.Office.Server.UserProfiles;
using Microsoft.SharePoint.Utilities;
using System.DirectoryServices.AccountManagement;

namespace VPLink2013.Batch
{
    public class Program
    {
        private bool blnDev = false;
        private Dictionary<string, User> dicUsers = new Dictionary<string, User>();
        private Dictionary<string, string> dicInterimManager = new Dictionary<string, string>();
        private Dictionary<string, string> dicEcommerce = new Dictionary<string, string>();
        private Dictionary<string, string> dicCommercialVoyage = new Dictionary<string, string>();
        private Dictionary<string, string> dicProductionVoyage = new Dictionary<string, string>();
        private Dictionary<string, string> dicJedi = new Dictionary<string, string>();
        private Dictionary<string, string> dicMercure = new Dictionary<string, string>();
        private Dictionary<string, string> dicDirections = new Dictionary<string, string>();
        private Dictionary<string, string> dicDepartements = new Dictionary<string, string>();
        private Dictionary<string, string> dicServices = new Dictionary<string, string>();
        private List<string> hierarchicalStucture = new List<string>();

        static void Main(string[] args)
        {
            Program prg = new Program();
            prg.ProcessStat();
        }

        private void ProcessStat()
        {
            try
            {
                if (Directory.Exists(@"E:\Dev\Clearstream\Batch\VPLink\StatistiquesVPLink"))
                {
                    blnDev = true;
                }
                Console.WriteLine("Ouverture stat.txt...");
                string strTextSql = System.IO.File.ReadAllText("stat.txt");

                string strSeparator = "------------------------------------------------------------------";
                string[] astrText = strTextSql.Split(new string[1] { strSeparator }, StringSplitOptions.RemoveEmptyEntries);
                Console.WriteLine("Lecture stat.txt...");

                foreach (string strText in astrText)
                {
                    //AddTrace("...");
                    //AddTrace(strText);
                    Console.WriteLine("Lecture..." + strText);

                    int iPos = strText.IndexOf("\r\n", 2);
                    if (iPos > 0)
                    {
                        string strDescription = strText.Substring(0, iPos);
                        string strSql = strText.Substring(iPos + 2);

                        strDescription = strDescription.Replace("\r\n", " ").Replace("--", "").Trim();
                        strSql = strSql.Replace("\r\n", " ").Trim();

                        if (!string.IsNullOrEmpty(strDescription))
                        {
                            try
                            {
                                ExecuteSql(strDescription, strSql);
                            }
                            catch (System.Exception exc1)
                            {
                                AddTrace("Error.SQL : " + exc1.Message);
                            }
                        }
                    }
                }
                ProcessUsers();
            }
            catch (System.Exception exc)
            {
                AddTrace("Error : " + exc.Message + "/" + exc.StackTrace);
            }
            SaveTrace("StatistiquesVPLink.txt");
        }

        private string GetValueInt(string val)
        {
            if (string.IsNullOrEmpty(val))
            {
                return "0";
            }
            if (val.Contains(","))
            {
                return "0";
            }
            return val;
        }

        Dictionary<string, User> dicUserTrombi = new Dictionary<string, User>();
        private void ProcessUsers()
        {
            string strSql = "update Users set Actif = 0";
            SqlConnection conn = new SqlConnection(GetConnectionString());
            conn.Open();
            SqlCommand cmd = new SqlCommand(strSql, conn);
            cmd.ExecuteNonQuery();
            conn.Close();

            strSql = "select LoginUser, UserName, Direction, Departement, Service, Fonction, Site "
                + ", Pays, UrlPhoto, Titre, Descriptif, Telephone, Nom, Prenom, Manager, SitePersonnel "
                + ", Email, DateEntree, DateSortie, IsManager, IsInMultipleService, Actif "
                + ", TrombiLigne, TrombiColonne, TrombiService, IdSSP, IsEcommerce, IsProductionVoyage, IsCommercialVoyage "
                + " from Users";
            AddTrace("strSql : " + strSql);
            conn = new SqlConnection(GetConnectionString());
            conn.Open();
            cmd = new SqlCommand(strSql, conn);
            SqlDataReader Reader = cmd.ExecuteReader();

            while (Reader.Read())
            {
                User usr = new User();
                usr.LoginUser = GetValueReader(Reader, 0);
                usr.UserName = GetValueReader(Reader, 1);
                usr.Direction = GetValueReader(Reader, 2);
                usr.Departement = GetValueReader(Reader, 3);
                usr.Service = GetValueReader(Reader, 4);
                usr.Fonction = GetValueReader(Reader, 5);
                usr.Site = GetValueReader(Reader, 6);
                usr.Pays = GetValueReader(Reader, 7);
                usr.UrlPhoto = GetValueReader(Reader, 8);
                usr.Titre = GetValueReader(Reader, 9);
                usr.Descriptif = GetValueReader(Reader, 10);
                usr.Telephone = GetValueReader(Reader, 11);
                usr.Nom = GetValueReader(Reader, 12);
                usr.Prenom = GetValueReader(Reader, 13);
                usr.Manager = GetValueReader(Reader, 14);
                usr.SitePersonnel = GetValueReader(Reader, 15);
                usr.Email = GetValueReader(Reader, 16);
                usr.DateEntree = GetValueReader(Reader, 17);
                usr.DateSortie = GetValueReader(Reader, 18);
                usr.IsManager = GetValueReader(Reader, 19);
                usr.IsInMultipleService = GetValueReader(Reader, 20);
                usr.Actif = GetValueReader(Reader, 21);
                usr.TrombiLigne = GetValueReader(Reader, 22);
                usr.TrombiColonne = GetValueReader(Reader, 23);
                usr.TrombiService = GetValueReader(Reader, 24);
                usr.IdSSP = GetValueReader(Reader, 25);
                usr.IsEcommerce = GetValueReader(Reader, 26);
                usr.IsProductionVoyage = GetValueReader(Reader, 27);
                usr.IsCommercialVoyage = GetValueReader(Reader, 28);

                if (!dicUsers.ContainsKey(usr.LoginUser))
                {
                    dicUsers.Add(usr.LoginUser, usr);
                }
            }
            conn.Close();

            SPQuery queryInterimList = new SPQuery()
            {
                ViewFields = "<FieldRef Name='Utilisateur' />"
            };

            #region Dctionnaire Mercure
            Console.WriteLine("Ajout des Jedi dans le dictionnaire");
            using (SPSite site = new SPSite("http://vplink/services/production"))
            {
                Console.WriteLine("Vplink production...");
                AddTrace("Ajout des Membres Mercure dans le dictionnaire");
                using (SPWeb webMercure = site.OpenWeb("Mercure"))
                {
                    SPGroup mercureGroup = webMercure.Groups["Membres de Mercure"];
                    if (mercureGroup != null)
                    {
                        foreach (SPUser user in mercureGroup.Users)
                        {
                            if (!user.IsDomainGroup)
                            {
                                if (!dicMercure.ContainsKey(user.LoginName))
                                {
                                    Console.WriteLine("Ajout de {0} aux membres mercure", user.LoginName);
                                    AddTrace(string.Format("Ajout de {0} aux membres mercure", user.LoginName));
                                    dicMercure.Add(user.LoginName, user.Name);
                                }
                            }
                        }
                    }
                }
            }
            #endregion

            using (SPSite site = new SPSite("http://vplink"))
            {
                Console.WriteLine("Vplink...");
                AddTrace("Ajout des Jedi dans le dictionnaire");
                using (SPWeb jediWeb = site.OpenWeb("/espacejedi"))
                {
                    Console.WriteLine("Espace Jedi...");
                    SPGroup jediGroup = jediWeb.Groups["Visiteurs de Espace Jedi"];
                    if (jediGroup != null)
                    {
                        Console.WriteLine("Visiteurs de Espace Jedi...");
                        foreach (SPUser user in jediGroup.Users)
                        {
                            Console.WriteLine(user.LoginName + "...");
                            if (!user.IsDomainGroup)
                            {
                                if (!dicJedi.ContainsKey(user.LoginName))
                                {
                                    AddTrace("    login name : " + user.LoginName);
                                    dicJedi.Add(user.LoginName, user.Name);
                                }
                            }
                            else
                            {
                                AddTrace(user.Name + " est un groupe de sécurité");
                                bool reachedMax = false;
                                SPPrincipalInfo[] principalUsers1 = SPUtility.GetPrincipalsInGroup(jediWeb, user.Name, 200, out reachedMax);
                                foreach (SPPrincipalInfo userFromGroup in principalUsers1)
                                {
                                    Console.WriteLine(userFromGroup.LoginName + "...");
                                    AddTrace("  " + userFromGroup.DisplayName
                                        + " appartient au groupe " + user.Name
                                        + " son type est : " + userFromGroup.PrincipalType.ToString());
                                    if (userFromGroup.PrincipalType == SPPrincipalType.User)
                                    {
                                        if (!dicJedi.ContainsKey(userFromGroup.LoginName))
                                        {
                                            dicJedi.Add(userFromGroup.LoginName, userFromGroup.DisplayName);
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                Console.WriteLine("Ajout des Interims dans le dictionnaire");
                using (SPWeb web = site.RootWeb)
                {
                    SPList interimManagerList = web.GetList("/Lists/Utilisateurs%20Outil%20Interim/");
                    if (interimManagerList != null)
                    {
                        SPListItemCollection interimManagers = interimManagerList.GetItems(queryInterimList);
                        SPFieldUser userField = interimManagerList.Fields["Utilisateur"] as SPFieldUser;
                        foreach (SPListItem item in interimManagers)
                        {
                            if (item["Utilisateur"] != null)
                            {
                                SPFieldUserValue userValue = userField.GetFieldValue(item["Utilisateur"].ToString()) as SPFieldUserValue;
                                if (userValue.User != null)
                                {
                                    // LoginName oredis-vp\pnom
                                    // Name Prenom Nom
                                    if (!dicInterimManager.ContainsKey(userValue.User.LoginName))
                                    {
                                        AddTrace("add to iterim " + userValue.User.LoginName);
                                        dicInterimManager.Add(userValue.User.LoginName, userValue.User.Name);
                                    }
                                }
                            }
                        }
                    }
                    Microsoft.Office.Server.ServerContext sc = Microsoft.Office.Server.ServerContext.GetContext((SPSite)site);
                    UserProfileManager upm = new UserProfileManager(sc);

                    Console.WriteLine("Check users...");
                    foreach (UserProfile upLoop in upm)
                    {
                        CheckUser(upLoop);
                    }
                }
            }
        }

        private string GetValueReader(SqlDataReader Reader, int position)
        {
            string strValue = "";
            if (!Reader.IsDBNull(position))
            {
                strValue = Reader.GetValue(position).ToString();
            }
            return strValue;
        }

        private bool IsMercureMember(string userLogin)
        {
            bool isMercureMember = false;
            using (SPSite site = new SPSite("http://vplink/"))
            {
                using (SPWeb web = site.OpenWeb())
                {
                    try
                    {
                        SPUser i = web.Users[userLogin];
                        if (i != null)
                        {
                            string mercureGroupName = "Membres de Mercure";
                            isMercureMember = i.Groups.Cast<SPGroup>().Any(g => g.Name.ToLower() == mercureGroupName.ToLower());
                            AddTrace(string.Format("{0} est Membre Mercure = {1}", userLogin, isMercureMember));
                            Console.WriteLine("{0} est Membre Mercure = {1}", userLogin, isMercureMember);
                        }
                    }
                    catch (Exception ex)
                    {
                        AddTrace(string.Format("Exception lors de verfication du membre mercure {0}", userLogin));
                        AddTrace(string.Format("Exception {0}", ex.StackTrace));
                    }
                }
            }
            return isMercureMember;
        }

        private bool IsRhGroup(string accountName)
        {
            string rhDistributionGroup = "Ressources Humaines";
            string managersDistributionGroup = "Managers";
            bool isRh = false;
            using (var domainContext = new PrincipalContext(ContextType.Domain))
            {
                GroupPrincipal rhGroup = GroupPrincipal.FindByIdentity(domainContext, rhDistributionGroup);
                GroupPrincipal managersGroup = GroupPrincipal.FindByIdentity(domainContext, managersDistributionGroup);
                UserPrincipal up = UserPrincipal.FindByIdentity(domainContext, IdentityType.SamAccountName, accountName);
                if (rhGroup != null && managersGroup != null && up != null)
                {
                    if (up.IsMemberOf(rhGroup) || up.IsMemberOf(managersGroup))
                    {
                        isRh = true;
                        AddTrace(string.Format("{0} appartient aux groupes RH/Managers", accountName));
                        Console.WriteLine(string.Format("{0} appartient aux groupes RH/Managers", accountName));
                    }
                }
            }
            return isRh;
        }


        private bool IsEcommerce(string accountName)
        {
            string ecommerceDistributionGroup = "Equipe Ecommerce Voyages";
            bool isEcommerce = false;
            using (var domainContext = new PrincipalContext(ContextType.Domain))
            {
                GroupPrincipal ecommerceGroup = GroupPrincipal.FindByIdentity(domainContext, ecommerceDistributionGroup);
                UserPrincipal up = UserPrincipal.FindByIdentity(domainContext, IdentityType.SamAccountName, accountName);
                if (ecommerceGroup != null && up != null)
                {
                    if (up.IsMemberOf(ecommerceGroup))
                    {
                        isEcommerce = true;
                        Console.WriteLine(string.Format("{0} appartient au groupe ECommerce", accountName));
                    }
                }
            }
            return isEcommerce;
        }

        private bool IsCommercialVoyage(string accountName)
        {
            string commercialDistributionGroup = "Pôle Commercial Voyages";
            bool isCommercial = false;
            using (var domainContext = new PrincipalContext(ContextType.Domain))
            {
                GroupPrincipal commercialGroup = GroupPrincipal.FindByIdentity(domainContext, commercialDistributionGroup);

                UserPrincipal up = UserPrincipal.FindByIdentity(domainContext, IdentityType.SamAccountName, accountName);
                if (commercialGroup != null && up != null)
                {
                    if (up.IsMemberOf(commercialGroup))
                    {
                        isCommercial = true;
                        Console.WriteLine(string.Format("{0} appartient au groupe Commercial Voyage", accountName));
                    }
                }
            }
            return isCommercial;
        }

        private bool IsProductionVoyage(string accountName)
        {
            string rdvDistributionGroup = "Equipe RDV Voyages";
            string managersDistributionGroup = "CHEFS EQUIPE PRODUCTION VOYAGES";
            bool isProductionVoyage = false;
            using (var domainContext = new PrincipalContext(ContextType.Domain))
            {
                GroupPrincipal rdvGroup = GroupPrincipal.FindByIdentity(domainContext, rdvDistributionGroup);
                GroupPrincipal managersGroup = GroupPrincipal.FindByIdentity(domainContext, managersDistributionGroup);
                UserPrincipal up = UserPrincipal.FindByIdentity(domainContext, IdentityType.SamAccountName, accountName);
                if (rdvGroup != null && managersGroup != null && up != null)
                {
                    if (up.IsMemberOf(rdvGroup) || up.IsMemberOf(managersGroup))
                    {
                        isProductionVoyage = true;
                        Console.WriteLine(string.Format("{0} appartient aux groupes RDV Voyage/Managers", accountName));
                    }
                }
            }
            return isProductionVoyage;
        }


        private void CheckUser(UserProfile up)
        {

            string AccountName = up[PropertyConstants.AccountName].Value != null ? up[PropertyConstants.AccountName].Value.ToString() : string.Empty;
            AddTrace("AccountName : " + AccountName);
            string FirstName = up[PropertyConstants.FirstName].Value != null ? up[PropertyConstants.FirstName].Value.ToString() : string.Empty;
            string LastName = up[PropertyConstants.LastName].Value != null ? up[PropertyConstants.LastName].Value.ToString() : string.Empty;
            string Login = up[PropertyConstants.AccountName].Value != null ? up[PropertyConstants.AccountName].Value.ToString() : string.Empty;
            string Direction = up[PropertyConstants.Department].Value != null ? up[PropertyConstants.Department].Value.ToString() : string.Empty;
            string Department = up["DepartmentLevel2"].Value != null ? up["DepartmentLevel2"].Value.ToString() : string.Empty;
            string Service = up["DepartmentLevel3"].Value != null ? up["DepartmentLevel3"].Value.ToString() : string.Empty;
            string Fonction = up[PropertyConstants.Title].Value != null ? up[PropertyConstants.Title].Value.ToString() : string.Empty;
            string Site = up["l"].Value != null ? up["l"].Value.ToString() : string.Empty;
            string IsManager = up["IsManager"].Value != null ? up["IsManager"].Value.ToString() : string.Empty;

            #region IsManager if RH
            if (IsManager != "True")
            {
                if (IsRhGroup(AccountName.Substring(AccountName.IndexOf(@"\") + 1)))
                {
                    IsManager = "True";
                }
            }
            #endregion
            string IsMercure = "False";
            if (Direction.ToLower() != "production des ventes")
            {
                if (dicMercure.ContainsKey(Login))
                {
                    IsMercure = "True";
                }
            }
            else
            {
                IsMercure = "True";
            }

            string isCommercialVoyage = "false";
            string isEcommerce = "false";
            string isProductionVoyage = "false";

            if (IsEcommerce(AccountName.Substring(AccountName.IndexOf(@"\") + 1)))
            {
                isEcommerce = "True";
            }

            if (IsCommercialVoyage(AccountName.Substring(AccountName.IndexOf(@"\") + 1)))
            {
                isCommercialVoyage = "true";
            }

            if (IsProductionVoyage(AccountName.Substring(AccountName.IndexOf(@"\") + 1)))
            {
                isProductionVoyage = "true";
            }

            string isInterimManager = "false";
            string isJedi = "false";


            AddTrace("login from ssp: " + Login);
            if (!string.IsNullOrEmpty(Login))
            {
                if (dicInterimManager.ContainsKey(Login))
                {
                    isInterimManager = "true";
                }
                if (dicJedi.ContainsKey(Login))
                {
                    isJedi = "true";
                }
            }

            AddTrace("IsManager : " + IsManager);
            string IsInMultipleService = up["IsInMultipleService"].Value != null ? up["IsInMultipleService"].Value.ToString() : string.Empty;
            AddTrace("IsInMultipleService : " + IsInMultipleService);

            Site = Site.Trim();
            string strSiteLower = Site.ToLower();

            if (Direction == ".")
            {
                Direction = "";
            }
            if (Department == ".")
            {
                Department = "";
            }
            if (Service == ".")
            {
                Service = "";
            }
            if (!string.IsNullOrEmpty(Direction)
                && !Direction.Equals("inexistant", StringComparison.CurrentCultureIgnoreCase)
                && !Direction.Equals("DSI", StringComparison.CurrentCultureIgnoreCase)
                && !Direction.Equals("direction de la planification opérationnelle", StringComparison.CurrentCultureIgnoreCase)
                && !Direction.Equals("dcf", StringComparison.CurrentCultureIgnoreCase)
                && !Direction.Equals("-", StringComparison.CurrentCultureIgnoreCase))
            {
                if (!hierarchicalStucture.Contains(string.Format("{0};{1};{2}", Direction, Department, Service)))
                {
                    hierarchicalStucture.Add(string.Format("{0};{1};{2}", Direction, Department, Service));
                    CheckHierachicalStructure(Service, Department, Direction);
                }
            }
            //Comment Starts here

            string Pays = "";
            if (strSiteLower.StartsWith("mitry") || strSiteLower.StartsWith("st vulbas")
                || strSiteLower.StartsWith("thillay")
                || strSiteLower.StartsWith("la plaine")
                || strSiteLower.StartsWith("le blanc")
                || strSiteLower.StartsWith("blanc")
                || strSiteLower.StartsWith("saint vulbas")
                || strSiteLower.StartsWith("saint-vulbas")
                || strSiteLower.StartsWith("thillay")
                || strSiteLower.StartsWith("le thillay"))
            {
                Pays = "France";
            }
            if (strSiteLower == "milano" || strSiteLower == "italie")
            {
                Pays = "Italie";
            }
            if (strSiteLower == "düsseldorf" || strSiteLower == "allemagne")
            {
                Pays = "Allemagne";
            }
            if (strSiteLower == "madrid" || strSiteLower == "barcelona" || strSiteLower == "espagne")
            {
                Pays = "Espagne";
            }
            if (strSiteLower == "royaume-uni")
            {
                Pays = "Royaume-Uni";
            }

            string IdSSP = up[PropertyConstants.UserGuid].Value != null ? up[PropertyConstants.UserGuid].Value.ToString() : string.Empty;
            string UrlPhoto = up[PropertyConstants.PictureUrl].Value != null ? up[PropertyConstants.PictureUrl].Value.ToString() : string.Empty;
            string Titre = up[PropertyConstants.Title].Value != null ? up[PropertyConstants.Title].Value.ToString() : string.Empty;
            string Descriptif = up[PropertyConstants.AboutMe].Value != null ? up[PropertyConstants.AboutMe].Value.ToString() : string.Empty;
            string Telephone = up[PropertyConstants.WorkPhone].Value != null ? up[PropertyConstants.WorkPhone].Value.ToString() : string.Empty;
            string Nom = up[PropertyConstants.LastName].Value != null ? up[PropertyConstants.LastName].Value.ToString() : string.Empty;
            string Prenom = up[PropertyConstants.FirstName].Value != null ? up[PropertyConstants.FirstName].Value.ToString() : string.Empty;
            string Manager = up[PropertyConstants.Manager].Value != null ? up[PropertyConstants.Manager].Value.ToString() : string.Empty;
            string SitePersonnel = "http://myvplink/Person.aspx?guid=" + IdSSP;
            string Email = up[PropertyConstants.WorkEmail].Value != null ? up[PropertyConstants.WorkEmail].Value.ToString() : string.Empty;
            string DateEntree = up["extensionAttribute1"].Value != null ? up["extensionAttribute1"].Value.ToString() : string.Empty;
            string DateSortie = up["ExitDate"].Value != null ? up["ExitDate"].Value.ToString() : string.Empty;
            string Actif = "1";
            string TrombiLigne = "";
            string TrombiColonne = "";
            string TrombiService = "";

            string AccountNameFull = FirstName + " " + LastName;
            AccountNameFull = AccountNameFull.Trim();

            if (string.IsNullOrEmpty(AccountNameFull))
            {
                return;
            }

            string strSql = "";
            if (dicUsers.ContainsKey(AccountName))
            {
                strSql = "update Users set UserName = '" + FormatSql(AccountNameFull) + "'"
                    + ", IdSSP='" + FormatSql(IdSSP) + "' "
                    + ", Direction='" + FormatSql(Direction) + "' "
                    + ", Departement='" + FormatSql(Department) + "' "
                    + ", Service='" + FormatSql(Service) + "' "
                    + ", Fonction='" + FormatSql(Fonction) + "' "
                    + ", Site='" + FormatSql(Site) + "' "
                    + ", Pays='" + FormatSql(Pays) + "' "
                    + ", UrlPhoto='" + FormatSql(UrlPhoto) + "' "
                    + ", Titre='" + FormatSql(Titre) + "' "
                    + ", Descriptif='" + FormatSql(Descriptif) + "' "
                    + ", Telephone='" + FormatSql(Telephone) + "' "
                    + ", Nom='" + FormatSql(Nom) + "' "
                    + ", Prenom='" + FormatSql(Prenom) + "' "
                    + ", Manager='" + FormatSql(Manager) + "' "
                    + ", SitePersonnel='" + FormatSql(SitePersonnel) + "' "
                    + ", Email='" + FormatSql(Email) + "' "
                    + ", DateEntree=" + FormatSqlDate(DateEntree)
                    + ", DateSortie=" + FormatSqlDate(DateSortie)
                    + ", IsManager=" + FormatSqlBit(IsManager)
                    + ", IsInMultipleService=" + FormatSqlBit(IsInMultipleService)
                    + ", Actif=" + FormatSqlBit(Actif)
                    + ", TrombiLigne='" + FormatSql(TrombiLigne) + "' "
                    + ", TrombiColonne='" + FormatSql(TrombiColonne) + "' "
                    + ", TrombiService='" + FormatSql(TrombiService) + "' "
                    + ", IsInterimManager='" + FormatSqlBit(isInterimManager) + "' "
                    + ", IsMercure='" + FormatSqlBit(IsMercure) + "' "
                    + ", IsJedi='" + FormatSqlBit(isJedi) + "' "
                    + ", IsEcommerce='" + FormatSqlBit(isEcommerce) + "' "
                    + ", IsProductionVoyage='" + FormatSqlBit(isProductionVoyage) + "' "
                    + ", IsCommercialVoyage='" + FormatSqlBit(isCommercialVoyage) + "' "
                    + " where LoginUser='" + FormatSql(Login) + "'";
            }
            else
            {
                strSql = "insert into Users (LoginUser, UserName, "
                    + " IdSSP, Direction, Departement, "
                    + " Service, Fonction, Site, "
                    + " Pays, UrlPhoto, Titre, Descriptif, "
                    + " Telephone, Nom, Prenom, Manager, SitePersonnel, "
                    + " Email, DateEntree, DateSortie,"
                    + " IsManager, IsInMultipleService, Actif, "
                    + " TrombiLigne, TrombiColonne, TrombiService, IsInterimManager, IsMercure, IsJedi, IsEcommerce, IsProductionVoyage, IsCommercialVoyage) "
                    + " values ('" + FormatSql(AccountName) + "', '" + FormatSql(AccountNameFull)
                    + "', '" + FormatSql(IdSSP) + "', '" + FormatSql(Direction) + "', '" + FormatSql(Department)
                    + "', '" + FormatSql(Service) + "', '" + FormatSql(Fonction) + "', '" + FormatSql(Site)
                    + "', '" + FormatSql(Pays) + "', '" + FormatSql(UrlPhoto) + "', '" + FormatSql(Titre) + "', '" + FormatSql(Descriptif)
                    + "', '" + FormatSql(Telephone) + "', '" + FormatSql(Nom) + "', '" + FormatSql(Prenom) + "', '" + FormatSql(Manager) + "', '" + FormatSql(SitePersonnel)
                    + "', '" + FormatSql(Email) + "', " + FormatSqlDate(DateEntree) + ", " + FormatSqlDate(DateSortie)
                    + ", " + FormatSqlBit(IsManager) + ", " + FormatSqlBit(IsInMultipleService) + ", " + FormatSqlBit(Actif)
                    + ", '" + FormatSql(TrombiLigne) + "', '" + FormatSql(TrombiColonne) + "', '" + FormatSql(TrombiService) + "', '" + FormatSqlBit(isInterimManager) + "', '" + FormatSqlBit(IsMercure) + "', '" + FormatSqlBit(isJedi) + "','" + FormatSqlBit(isEcommerce) + "','" + FormatSqlBit(isProductionVoyage) + "','" + FormatSqlBit(isCommercialVoyage) + "')";
            }
            AddTrace("strSql : " + strSql);

            SqlConnection conn = new SqlConnection(GetConnectionString());
            conn.Open();
            SqlCommand cmd = new SqlCommand(strSql, conn);
            cmd.ExecuteNonQuery();
            conn.Close();

            //Comment Ends here
        }
        private void CheckServcieDepartement(string serviceName, string departementId)
        {
            serviceName = serviceName.ToUpper();
            string serviceId = SQLExecution.GetServiceId(serviceName, departementId);
            AddTrace(string.Format("service id = {0}", serviceId));
            // 
            if (string.IsNullOrEmpty(serviceId))
            {
                if (CheckTeamExists("service", serviceName))
                {
                    AddTrace(string.Format("service {0} existe", serviceName));
                    if (SQLExecution.IsOrphanService(serviceName))
                    {
                        //update
                        serviceId = SQLExecution.GetServiceIdByName(serviceName);
                        AddTrace(string.Format("Mise à jour du service {0}, id : {1}", serviceName, serviceId));
                        SQLExecution.UpdateService(serviceId, departementId);
                    }
                    else
                    {
                        //insert
                        //AddTrace( string.Format( "Le service {0} est bien rattaché", serviceName ) );
                        //SQLExecution.InsertService( serviceName, departementId );
                    }
                }
                else
                {
                    AddTrace(string.Format("service {0} n'existe pas ", serviceName));
                    AddTrace(string.Format("Insert du service {0}", serviceName));
                    SQLExecution.InsertService(serviceName, departementId);
                }
            }
        }

        private void CheckHierachicalStructure(string serviceName, string departementName, string directionName)
        {
            departementName = departementName.ToUpper();
            directionName = directionName.ToUpper();

            AddTrace(string.Format("Verification service {0}, departement {1}, direction {2}",
                serviceName, departementName, directionName));

            try
            {
                string directionId = SQLExecution.GetDirectionId(directionName);
                AddTrace(string.Format("direction id = {0}", directionId));
                if (string.IsNullOrEmpty(directionId))
                {
                    directionId = SQLExecution.InsertDirection(directionName);
                }
                if (!string.IsNullOrEmpty(directionId) && !string.IsNullOrEmpty(departementName))
                {
                    string departementId = SQLExecution.GetDepartementId(departementName, directionId);
                    // 
                    AddTrace(string.Format("departement id = {0}", departementId));
                    if (string.IsNullOrEmpty(departementId))
                    {
                        AddTrace(string.Format("departement id est null ou vide"));
                        if (CheckTeamExists("departement", departementName))
                        {
                            AddTrace(string.Format("departement {0} existe", departementName));
                            if (SQLExecution.IsOrphanDepartement(departementName))
                            {
                                //update
                                departementId = SQLExecution.GetDepartementIdByName(departementName);
                                AddTrace(string.Format("Mise à jour du departement {0} id : {1}", departementName, departementId));
                                SQLExecution.UpdateDepartement(departementId, directionId);
                            }
                            else
                            {
                                //insert
                                //AddTrace( string.Format( "Insert du departement {0}", departementName ) );
                                //departementId = SQLExecution.InsertDepartement( departementName, directionId );
                            }
                        }
                        else
                        {
                            AddTrace(string.Format("departement {0} n'existe pas", departementName));
                            AddTrace(string.Format("Insert du departement {0}", departementName));
                            departementId = SQLExecution.InsertDepartement(departementName, directionId);
                        }
                    }
                    if (!string.IsNullOrEmpty(serviceName) && !string.IsNullOrEmpty(departementId))
                    {
                        CheckServcieDepartement(serviceName, departementId);
                    }
                }
            }
            catch (Exception ex)
            {
                AddTrace(string.Format("Exception dans la verification"));
                AddTrace(string.Format("Exception {0}", ex.StackTrace));
            }
        }

        private bool CheckTeamExists(string teamType, string teamName)
        {
            bool teamExists = false;
            string sqlReq = string.Empty;
            switch (teamType)
            {
                case "direction":
                    {
                        sqlReq = string.Format(@"select count(*) from Directions where nom = '{0}'", teamName.Replace("'", "''").ToUpper());
                        break;
                    }
                case "departement":
                    {
                        sqlReq = string.Format(@"select count(*) from Departements where nom = '{0}'", teamName.Replace("'", "''").ToUpper());
                        break;
                    }
                case "service":
                    {
                        sqlReq = string.Format(@"select count(*) from Services where nom = '{0}'", teamName.Replace("'", "''").ToUpper());
                        break;
                    }
            }
            try
            {
                Console.WriteLine("select : {0}", sqlReq);
                SqlConnection conn = new SqlConnection(GetConnectionString());
                conn.Open();
                SqlCommand cmd = new SqlCommand(sqlReq, conn);
                cmd.CommandType = System.Data.CommandType.Text;
                object res = cmd.ExecuteScalar();
                Console.WriteLine("resultat de la requete : {0}", res.ToString());
                int nbRows = (Int32)res;
                if (nbRows > 0)
                {
                    teamExists = true;
                    AddTrace(string.Format("L'equipe {0} existe dans la liste des {1}.", teamName.ToUpper(), teamType));
                }
                else
                {
                    AddTrace(string.Format("L'equipe {0} n'existe pas dans la liste des {1}.", teamName.ToUpper(), teamType));
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                AddTrace(string.Format("Exception lors de la recherche de l'equipe {0}", teamName.ToUpper()));
                AddTrace(string.Format("Requete {0}", sqlReq));
                AddTrace(string.Format("Exception {0}", ex.StackTrace));
            }
            return teamExists;
        }

        private string FormatSql(string value)
        {
            return value.Replace("'", "''");
        }
        private string FormatSqlDate(string value)
        {
            try
            {
                DateTime dtDate = DateTime.Parse(value);
                return "'" + dtDate.ToString("yyyyMMdd") + "'";
            }
            catch (System.Exception)
            {
                return "NULL";
            }
        }
        private string FormatSqlBit(string value)
        {
            if (value == "True" || value == "true" || value == "1")
            {
                return "1";
            }
            else
            {
                return "0";
            }
        }


        private void ExecuteSql(string strDescription, string strSql)
        {
            Console.WriteLine("...strDescription : " + strDescription);
            Console.WriteLine("...strSql : " + strSql);
            AddTrace("...strDescription : " + strDescription);
            AddTrace("...strSql : " + strSql);

            SqlConnection conn = new SqlConnection(GetConnectionString());
            conn.Open();
            SqlCommand cmd = new SqlCommand(strSql, conn);
            SqlDataReader Reader = cmd.ExecuteReader();

            Console.WriteLine();

            DateTime dtNow = DateTime.Now;
            StringBuilder sbChartContent = new StringBuilder();

            StringBuilder sbLog = new StringBuilder();
            int iRow = 0;
            GlobalActivites global = new GlobalActivites();
            global.Fields = new List<Field>();
            global.Items = new List<Item>();
            while (Reader.Read())
            {
                Item it = new Item();
                it.ID = iRow + 1;
                it.Versions = new List<Version>();
                Version ver = new Version();
                ver.Fields = new List<Field>();

                if (iRow == 0)
                {
                    for (int iFld = 0; iFld < Reader.FieldCount; iFld++)
                    {
                        if (iFld > 0)
                        {
                            sbLog.Append(";");
                        }
                        string strFieldName = Reader.GetName(iFld);
                        sbLog.Append(strFieldName);
                        Field fld = new Field();
                        fld.Name = strFieldName;
                        global.Fields.Add(fld);
                    }
                }
                sbLog.Append("\r\n");
                for (int iFld = 0; iFld < Reader.FieldCount; iFld++)
                {
                    if (iFld > 0)
                    {
                        sbLog.Append(";");
                    }
                    string strFieldValue = "";
                    if (!Reader.IsDBNull(iFld))
                    {
                        strFieldValue = Reader.GetValue(iFld).ToString();
                    }
                    sbLog.Append(strFieldValue);

                    Field fld = new Field();
                    fld.Name = Reader.GetName(iFld);
                    fld.Value = strFieldValue;
                    ver.Fields.Add(fld);
                }
                it.Versions.Add(ver);
                global.Items.Add(it);

                if (strDescription == "02. Annee, mois, site")
                {
                    string strAnnee = Reader.GetValue(0).ToString();
                    string strMois = Reader.GetValue(1).ToString();
                    string strSite = Reader.GetValue(2).ToString();
                    string strValeur = Reader.GetValue(3).ToString();

                    if (strAnnee == dtNow.Year.ToString() && strMois == dtNow.Month.ToString())
                    {
                        sbChartContent.AppendLine("data.setValue(" + iRow.ToString() + ", 0, \"" + strSite + "\");");
                        sbChartContent.AppendLine("data.setValue(" + iRow.ToString() + ", 1, " + strValeur + ");");
                    }
                }
                if (strDescription == "06. Page, article")
                {
                    string strTitre = Reader.GetValue(1).ToString().Replace("é", "e").Replace("\"", "'");
                    string strValeur = Reader.GetValue(2).ToString();

                    sbChartContent.AppendLine("data.setValue(" + iRow.ToString() + ", 0, \"" + strTitre + "\");");
                    sbChartContent.AppendLine("data.setValue(" + iRow.ToString() + ", 1, " + strValeur + ");");
                }
                iRow += 1;
            }
            conn.Close();

            string strFileName = strDescription + ".txt";
            string strPathLog = @"StatsVPLink\" + strFileName;
            System.IO.File.WriteAllText(strPathLog, sbLog.ToString());
            UploadFile(strPathLog, strFileName);

            SerializeGlobal(strDescription, global);

            string strContenuReel = sbChartContent.ToString();
            if (!string.IsNullOrEmpty(strContenuReel))
            {
                string strCompteurReel = iRow.ToString();

                string strCompteur = "/%COMPTEUR%/";
                string strContenu = "/%CONTENU%/";
                string strFileChart = "Chart.htm";
                string strChartContent = System.IO.File.ReadAllText(strFileChart);
                strChartContent = strChartContent.Replace(strCompteur, strCompteurReel).Replace(strContenu, strContenuReel);

                strFileName = strDescription + ".htm";
                strPathLog = @"StatsVPLink\" + strFileName;
                System.IO.File.WriteAllText(strPathLog, strChartContent);
                UploadFile(strPathLog, strFileName);
            }
        }

        private string GetConnectionString()
        {
            string strConnection = @"server=SD-MOSS-SQL\MOSS;database=VPLinkLog;Integrated Security=True;Connect Timeout=30;MultipleActiveResultSets=True;";
            //data source=VPLINKDEV02\OFFICESERVERS;initial catalog=VPLinkLog;integrated security=True;multipleactiveresultsets=True;
            //string strConnection = @"server=SDPP-MOSS-SQL\MOSS;database=VPLinkLog;Integrated Security=True;Connect Timeout=30;MultipleActiveResultSets=True;";
            //if (blnDev)
            //{
            //string strConnection = @"server=S3R7-MOSS-SQL01;database=VPLinkLog;Integrated Security=True;Connect Timeout=30;MultipleActiveResultSets=True;";
            //}
            AddTrace("strConnection : " + strConnection);
            return strConnection;
        }

        /// <summary>
        /// Serialization = l'objet de type GlobalAffichage devient string XML 
        /// </summary>
        /// <param name="lst">Global object in its native format</param>
        /// <returns>String XML that represents the serialized object</returns>

        private string SerializeGlobal(string strTitle, GlobalActivites lst)
        {
            string strXml = string.Empty;

            XmlSerializer serializer = new XmlSerializer(typeof(GlobalActivites));
            StringBuilder sb = new StringBuilder();
            TextWriter tw = new StringWriter(sb);
            serializer.Serialize(tw, lst);
            tw.Close();

            strXml = sb.ToString();
            strXml = strXml.Replace("utf-16", "iso-8859-1");
            sb = null;

            string strXslPath = "OutPutXlReferentiel.xsl";
            string strResultat = GetTransform(strXml, strXslPath);

            //File.WriteAllText(strTitle + ".xml", strXml, Encoding.UTF8);

            string strNomFichierEntete = "EnteteExcel.txt";
            string strContenuFichierEntete = System.IO.File.ReadAllText(strNomFichierEntete);

            int index = strResultat.IndexOf("<Table ss:ExpandedColumnCount");
            if (index > 0)
            {
                strResultat = strContenuFichierEntete + strResultat.Substring(index) + "</Workbook>";
            }
            try
            {
                string strFileName = strTitle + ".xls";
                string strFullFileName = @"StatsVPLink\" + strFileName;
                File.WriteAllText(strFullFileName, strResultat, Encoding.UTF8);

                UploadFile(strFullFileName, strFileName);

            }
            catch (System.Exception exc)
            {
                AddTrace("Erreur création fichier Excel : " + exc.Message);
            }
            return strXml;
        }

        private void UploadFile(string strFullFileName, string strFileName)
        {
            try
            {
                Console.WriteLine("Ajout à la biblio.. " + strFileName);
                byte[] content = GetFileContent(strFullFileName);
                if (content == null)
                {
                    return;
                }

                SPSecurity.RunWithElevatedPrivileges
                (delegate()
                {
                    string strUrl = Param.Default.UrlSite;
                    using (SPSite mySite = new SPSite(strUrl))
                    {
                        using (SPWeb myWeb = mySite.OpenWeb())
                        {

                            SPList myList = myWeb.Lists["Statistiques"];

                            SPDocumentLibrary lst = (SPDocumentLibrary)myList;
                            //foreach (SPFolder fd in myList.ParentWeb.Folders)
                            //{
                            //    AddTrace("E.TRACE FOLDER : " + fd.Name + "     " + fd.ServerRelativeUrl);
                            //}

                            SPFileCollection fc = null;
                            SPFile file = null;

                            lst.ParentWeb.AllowUnsafeUpdates = true;
                            string TempName = "Statistiques";
                            fc = lst.ParentWeb.Folders[TempName].Files;
                            file = fc.Add(strFileName, content, true, "creation automatique", false);
                            lst.Update();

                            AddTrace("UploadFile.Statistiques : " + strFileName);
                        }
                    }
                });

            }
            catch (System.Exception exc)
            {
                AddTrace("Error.UploadFile : " + exc.Message + "/" + exc.StackTrace);
            }
        }

        private byte[] GetFileContent(string fileName)
        {
            AddTrace("GetFileContent.Start");

            byte[] arBFile = null;

            try
            {
                System.IO.FileStream fs = new System.IO.FileStream(fileName, System.IO.FileMode.Open, System.IO.FileAccess.Read);
                int iLg = int.Parse(fs.Length.ToString());

                arBFile = new byte[iLg];
                fs.Read(arBFile, 0, iLg);
                fs.Close();

                AddTrace("GetFileContent.OK");
            }
            catch (System.Exception exc)
            {
                AddTrace("ProcessFiles.Erreur lecture fichier : " + fileName + " => " + exc.Message);
            }
            return arBFile;
        }

        private string GetTransform(string strXml, string strXslPath)
        {
            if (!System.IO.File.Exists(strXslPath))
            {
                return ""; // "File not exists : " + strXslPath;
            }

            XmlTextReader xtr = new XmlTextReader(new StringReader(strXml));
            XPathDocument xpd = new XPathDocument(xtr);
            xtr.Close();
            XPathNavigator nav = xpd.CreateNavigator();


            try
            {
                XslCompiledTransform xslt = new XslCompiledTransform();
                xslt.Load(strXslPath);

                StringWriter sw = new StringWriter();
                xslt.Transform(nav, null, sw);
                string Html = sw.ToString();
                return Html;
            }
            catch (System.Xml.XPath.XPathException e)
            {
                AddTrace("GetTransform.XPathException  : " + e.Message + "/" + e.StackTrace);
            }
            catch (System.Xml.Xsl.XsltCompileException ex)
            {
                AddTrace("GetTransform.XsltCompileException  : " + ex.Message + "/" + ex.StackTrace
                    + " / LineNumber " + ex.LineNumber + " / LinePosition : " + ex.LinePosition
                    + " / SourceUri : " + ex.SourceUri);
            }
            catch (System.Xml.Xsl.XsltException xc)
            {
                AddTrace("GetTransform.XsltException  : " + xc.Message + "/" + xc.StackTrace
                    + " / LineNumber " + xc.LineNumber + " / LinePosition : " + xc.LinePosition
                    + " / SourceUri : " + xc.SourceUri);
            }
            catch (System.Exception exc)
            {
                AddTrace("GetTransform.Exception  : " + exc.Message + "/" + exc.StackTrace);
            }
            return "";
        }


        private StringBuilder sbTrace = new StringBuilder();
        public void AddTrace(string message)
        {
            sbTrace.AppendLine(message);
        }

        private void SaveTrace(string fileName)
        {
            try
            {
                string strPath = System.Environment.CurrentDirectory + @"\" + fileName;
                System.IO.File.WriteAllText(strPath, sbTrace.ToString(), Encoding.UTF8);
                sbTrace = new StringBuilder();
            }
            catch (System.Exception exc)
            {
                Console.WriteLine(exc.Message);
            }
        }

    }
}
